﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.IO;


namespace Faust.Andrew.MTG.Migration
{
    class SQLiteToCE
    {
        public void Migrate(string sourceSQLite, string destCE, bool isUpdate)
        {
            string imagePath = GetImagePath(sourceSQLite);
            using (System.Data.SQLite.SQLiteConnection liteConnection = new SQLiteConnection(GetLiteConnectionString(sourceSQLite)))
            {
                liteConnection.Open();

                using (System.Data.SqlServerCe.SqlCeConnection ceConnection = new SqlCeConnection(GetCeConnectionString(destCE)))
                {
                    ceConnection.Open();

                    if (isUpdate)
                    {
                        UpdateCards(liteConnection, ceConnection);    
                    }
                    else
                    {
                        ConvertCards(liteConnection, ceConnection, imagePath);
                    }

                    ConvertDecks(liteConnection, ceConnection);                    
                }
            }
        }

        private void UpdateCards(SQLiteConnection liteConnection, SqlCeConnection ceConnection)
        {
            using (SQLiteCommand sourceCommand = new SQLiteCommand())
            {
                sourceCommand.Connection = liteConnection;
                sourceCommand.CommandText = "select MultiverseID, Quantity from Cards";

                using (SQLiteDataReader reader = sourceCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        using (SqlCeCommand command = new SqlCeCommand())
                        {
                            command.Connection = ceConnection;
                            command.CommandText = "Update Cards set Quantity=@Quantity where MultiverseID=@MultiverseID";

                            command.Parameters.AddWithValue("@Quantity", reader["Quantity"]);
                            command.Parameters.AddWithValue("@MultiverseID", reader["MultiverseID"]);

                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private void ConvertCards(SQLiteConnection liteConnection, SqlCeConnection ceConnection, string imagePath)
        {
            using (SQLiteCommand sourceCommand = new SQLiteCommand())
            {
                sourceCommand.Connection = liteConnection;
                sourceCommand.CommandText = "select * from Cards";

                using (SQLiteDataReader reader = sourceCommand.ExecuteReader())
                {                    
                    while (reader.Read())
                    {
                        using (SqlCeCommand command = new SqlCeCommand()) 
                        {
                            command.Connection = ceConnection;
                            command.CommandText = "insert into cards ([MultiverseID], [Rarity], [Toughness], [ToughnessNumeric], [Power], [PowerNumeric], "
                                + "[CardTypes], [ConvertedManaCost], [ConvertedManaCostNumeric], [Name], [CardText], [ManaCost], [Quantity], [Color], [Image]) values ("
                                + "@MultiverseID, @Rarity, @Toughness, @ToughnessNumeric, @Power, @PowerNumeric, "
                                + "@CardTypes, @ConvertedManaCost, @ConvertedManaCostNumeric, @Name, @CardText, @ManaCost, @Quantity, @Color, @Image)";

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                command.Parameters.AddWithValue(String.Format("@{0}", reader.GetName(i)), reader[i]);
                            }

                            byte[] image = GetImage(imagePath + "\\" + reader["MultiverseID"] as string + ".png");

                            command.Parameters.AddWithValue("@Image", image);

                            command.ExecuteNonQuery();
                        }       
                    }
                }
            }
        }

        private void ConvertDecks(SQLiteConnection liteConnection, SqlCeConnection ceConnection)
        {
            using (SQLiteCommand sourceCommand = new SQLiteCommand())
            {
                sourceCommand.Connection = liteConnection;
                sourceCommand.CommandText = "select * from Decks";

                using (SQLiteDataReader reader = sourceCommand.ExecuteReader())
                {
                    SqlCeCommand identCommand = new SqlCeCommand();
                    identCommand.Connection = ceConnection;
                    identCommand.CommandText = "SET IDENTITY_INSERT [Decks] ON";
                    identCommand.ExecuteNonQuery();

                    while (reader.Read())
                    {
                        using (SqlCeCommand command = new SqlCeCommand())
                        {
                            command.Connection = ceConnection;                            

                            command.CommandText = "insert into Decks ([DeckID], [Name], [Description]) values (@DeckID, @Name, @Description)";

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                command.Parameters.AddWithValue(String.Format("@{0}", reader.GetName(i)), reader[i]);
                            }

                            command.ExecuteNonQuery();
                        }
                    }

                    identCommand.CommandText = "SET IDENTITY_INSERT [Decks] OFF";
                    identCommand.ExecuteNonQuery();
                }
            }

            ConvertDeckCards(liteConnection, ceConnection);
        }

        private void ConvertDeckCards(SQLiteConnection liteConnection, SqlCeConnection ceConnection)
        {
            using (SQLiteCommand sourceCommand = new SQLiteCommand())
            {
                sourceCommand.Connection = liteConnection;
                sourceCommand.CommandText = "select * from DeckCards";

                using (SQLiteDataReader reader = sourceCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        using (SqlCeCommand command = new SqlCeCommand())
                        {
                            command.Connection = ceConnection;

                            command.CommandText = "insert into DeckCards ([DeckID], [MultiverseID], [Quantity]) values (@DeckID, @MultiverseID, @Quantity)";

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                command.Parameters.AddWithValue(String.Format("@{0}", reader.GetName(i)), reader[i]);
                            }

                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private byte[] GetImage(string path)
        {
            using (FileStream stream = File.OpenRead(path))
            {
                stream.Seek(0, SeekOrigin.Begin);
                byte[] image = new byte[stream.Length];
                stream.Read(image, 0, image.Length);

                return image;
            }
        }

        private string GetImagePath(string litePath)
        {
            return String.Format("{0}\\Images", Path.GetDirectoryName(litePath));
        }

        private string GetLiteConnectionString(string litePath)
        {
            return String.Format("data source={0}", litePath);
        }

        private string GetCeConnectionString(string cePath)
        {
            return "Data Source=" + cePath + ";Max Database Size=4000";
        }
    }
}